﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace AlertSystem.Business
{
    public class BaseClass
    {
        protected static DataTable ExecuteReader(string query, CommandType type, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
            {
                conn.Open();
                return ExecuteReader(conn, type, query, param);
            }
        }
        protected static DataTable ExecuteReader(SqlConnection conn, CommandType type, string query, params SqlParameter[] param)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                return ExecuteReader(cmd, type, query, param);
            }
        }

        protected static DataTable ExecuteReader(SqlCommand cmd, CommandType type, string query, params SqlParameter[] param)
        {
            cmd.CommandText = query;
            cmd.CommandType = type;
            cmd.CommandTimeout = 0;
            if (param != null)
                cmd.Parameters.AddRange(param);
            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            return dt;
        }
        
        protected static int ExecuteQuery(string query)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        return ExecuteQuery(cmd, query);
                    }
                }
            }
            catch(Exception e) {
                throw e;
            }
        }

        protected static int ExecuteQuery(string query, params SqlParameter[] param)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        if (param != null)
                            cmd.Parameters.AddRange(param);
                        return ExecuteQuery(cmd, query);
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        protected static int ExecuteQuery(SqlCommand cmd, string query)
        {
            return cmd.ExecuteNonQuery();
        }

        protected static int ExecuteQuery(SqlConnection conn, string query)
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                return ExecuteQuery(cmd, query);
            }
        }

        protected static int ExecuteProc(string procName, params SqlParameter[] param)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procName, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 0;
                        if (param != null)
                            cmd.Parameters.AddRange(param);
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        protected static object ExecuteScalar(string query, CommandType type, params SqlParameter[] param)
        {
            using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
            {
                conn.Open();
                return ExecuteScalar(conn, type, query, param);
            }
        }

        protected static object ExecuteScalar(SqlCommand cmd, CommandType type, string query, params SqlParameter [] param)
        {
            cmd.CommandText = query;
            cmd.CommandType = type;
            cmd.CommandTimeout = 0;
            if (param != null)
                cmd.Parameters.AddRange(param);
            return cmd.ExecuteScalar();
        }

        protected static object ExecuteScalar(SqlConnection conn, CommandType type, string query,  params SqlParameter[] param)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                return ExecuteScalar(cmd,type, query, param);
            }
        }

        protected static void DeleteTempTable(string tableName)
        {
            try
            {
                ExecuteQuery(string.Format("DROP TABLE {0}", tableName));
            }
            catch { }
        }

        protected static OleDbConnection GetOleDbConnection(string filePath)
        {
            return new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", filePath));
        }
    }
}
